package vg.implementation.ll_services.data_base_service;

import com.google.common.collect.Lists;
import org.apache.commons.lang.NotImplementedException;
import org.apache.commons.lang.Validate;
import tzhomestudio.framework.shared.utils.DateUtils;
import tzhomestudio.framework.shared.utils.FileUtils;
import tzhomestudio.framework.shared.utils.RandomUtils;
import vg.interfaces.ll_services.data_base_service.data.header.*;
import vg.interfaces.ll_services.data_base_service.GraphDataBaseFormat_1_0;

import java.io.File;
import java.sql.*;
import java.util.*;

public class SQLiteDataBase_1_0 {
    // Constants: creates
    private static final String CREATE_VERTEX_TABLE = "create table " + GraphDataBaseFormat_1_0.VERTEX_TABLE + " (" +
            GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + " INT PRIMARY KEY, " +
            GraphDataBaseFormat_1_0.VERTEX_TABLE_GRAPH_ID + " INT, " +
            GraphDataBaseFormat_1_0.VERTEX_TABLE_LINK2INNER_GRAPH + " INT);";

    private static final String CREATE_EDGE_TABLE = "create table " + GraphDataBaseFormat_1_0.EDGE_TABLE + " (" +
            GraphDataBaseFormat_1_0.EDGE_TABLE_ID + " INT PRIMARY KEY," +
            GraphDataBaseFormat_1_0.EDGE_TABLE_GRAPH_ID + " INT, " +
            GraphDataBaseFormat_1_0.EDGE_TABLE_SRC_VERTEX + " INT, " +
            GraphDataBaseFormat_1_0.EDGE_TABLE_TRG_VERTEX + " INT);";


    private static final String CREATE_ATTRIBUTE_TABLE = "create table " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " (" +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_ID + " INTEGER PRIMARY KEY, " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + " INT, " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + " INT, " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME + " VARCHAR, " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_STRING_VALUE + " VARCHAR, " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE + " INT);";

    private static final String CREATE_GRAPH_TABLE = "create table " + GraphDataBaseFormat_1_0.GRAPH_TABLE + " (" +
            GraphDataBaseFormat_1_0.GRAPH_TABLE_ID + " INT PRIMARY KEY, " +
            GraphDataBaseFormat_1_0.GRAPH_TABLE_MODEL_ID + " INT, " +
            GraphDataBaseFormat_1_0.GRAPH_TABLE_DIRECTED + " BOOLEAN, " +
            GraphDataBaseFormat_1_0.GRAPH_TABLE_IS_ROOT + " BOOLEAN);";

    private static final String CREATE_GRAPH_MODEL_TABLE = "create table " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE + " (" +
            GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_ID + " INT PRIMARY KEY, " +
            GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_NAME + " VARCHAR);";

    private static final String CREATE_VERTEX_INDEX_TABLE = "create index Idx_vertex on " + GraphDataBaseFormat_1_0.VERTEX_TABLE + "(" +
            GraphDataBaseFormat_1_0.VERTEX_TABLE_LINK2INNER_GRAPH + ");";

    private static final String CREATE_EDGE_INDEX_TABLE = "create index Idx_edge on " + GraphDataBaseFormat_1_0.EDGE_TABLE + "(" +
            GraphDataBaseFormat_1_0.EDGE_TABLE_SRC_VERTEX + ", " +
            GraphDataBaseFormat_1_0.EDGE_TABLE_TRG_VERTEX + ");";

    private static final String CREATE_ATTRIBUTE_INDEX_TABLE = "create index Idx_attribute on " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + "(" +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + ", " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + ", " +
            GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME + ");";

    // Constants: inserts
    private static final String INSERT_VERTEX = "insert into " + GraphDataBaseFormat_1_0.VERTEX_TABLE + " values(%d, %d, %d);";

    private static final String INSERT_EDGE = "insert into " + GraphDataBaseFormat_1_0.EDGE_TABLE + " values(%d, %d, %d, %d);";

    private static final String INSERT_ATTRIBUTE = "insert into " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " values(%d, %d, %d, '%s','%s',%d);";

    private static final String INSERT_GRAPH = "insert into " + GraphDataBaseFormat_1_0.GRAPH_TABLE + " values(%d, %d, %b, %b);";

    private static final String INSERT_GRAPH_MODEL = "insert into " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE + " values(%d, '%s');";

    // Constants: selects
    private static final String SELECT_VERTEX_BY_GRAPH_ID = "SELECT * " +
            "FROM " + GraphDataBaseFormat_1_0.VERTEX_TABLE + " s1 " +
            "WHERE s1." + GraphDataBaseFormat_1_0.VERTEX_TABLE_LINK2INNER_GRAPH + "=%d;";

    private static final String SELECT_GRAPH_MODEL_BY_ID = "SELECT * " +
            "FROM " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE+" s1 " +
            "WHERE s1." + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_ID + "=%d;";

    private static final String SELECT_GRAPH_MODELS = " SELECT * " +
            " FROM " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE + " s1;";

    private static final String SELECT_GRAPH_BY_ID = "SELECT * " +
            "FROM " + GraphDataBaseFormat_1_0.GRAPH_TABLE + " s1 " +
            "WHERE s1." + GraphDataBaseFormat_1_0.GRAPH_TABLE_ID + "=%d;";

    private static final String SELECT_VERTEX_BY_ID = "SELECT * " +
            "FROM " + GraphDataBaseFormat_1_0.VERTEX_TABLE + " s1 " +
            "WHERE s1." + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + "=%d;";

    private static final String SELECT_EDGE_BY_ID = "SELECT * " +
            "FROM " + GraphDataBaseFormat_1_0.EDGE_TABLE + " s1 " +
            "WHERE s1." + GraphDataBaseFormat_1_0.EDGE_TABLE_ID + "=%d;";

    private static final String SELECT_ATTRIBUTE_BY_ID = " SELECT * " +
            " FROM " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " s1 " +
            " WHERE s1." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_ID + "=%d;";

    private static final String SELECT_ATTRIBUTES_BY_OWNER = " SELECT * " +
            " FROM " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE + " s1 " +
            " WHERE s1." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID + "=%d and s1." + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE + "=%d;";

    // Main data
    private Connection connection = null;

	public SQLiteDataBase_1_0() {
        try {
            // load the sqlite-JDBC driver using the current class loader
            Class.forName("org.sqlite.JDBC");

            String dataBaseDirectoryName = FileUtils.getWorkingDir() + "data" + File.separator + "db" + File.separator;
            File dataBaseDirectory = new File(dataBaseDirectoryName);
            if (!dataBaseDirectory.exists() && !dataBaseDirectory.mkdirs()) {
                throw new RuntimeException("Couldn't create directory for database");
            }

            // create connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dataBaseDirectoryName + "database_" + DateUtils.getStartAppTimeInDefaultFormat() + "_" + RandomUtils.generateRandomUUID() + ".db");
            connection.setAutoCommit(false);

            // create tables
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.execute(CREATE_VERTEX_TABLE);
            statement.execute(CREATE_EDGE_TABLE);
            statement.execute(CREATE_ATTRIBUTE_TABLE);
            statement.execute(CREATE_GRAPH_TABLE);
            statement.execute(CREATE_GRAPH_MODEL_TABLE);
            statement.execute(CREATE_VERTEX_INDEX_TABLE);
            statement.execute(CREATE_EDGE_INDEX_TABLE);
            statement.execute(CREATE_ATTRIBUTE_INDEX_TABLE);
            connection.commit();
        } catch (ClassNotFoundException | SQLException ex) {
            throw new RuntimeException(ex);
        }
	}
	
	public void close() {
		try {
            connection.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
	}
	
	public void addGraphModelHeaders(Collection<GraphModelHeader> headers) {
        Validate.notNull(headers);

        try (Statement statement = connection.createStatement()){
            for (GraphModelHeader graphModelHeader : headers) {
                statement.execute(String.format(INSERT_GRAPH_MODEL, graphModelHeader.getId(), graphModelHeader.getName()));
            }
            connection.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
	}
	
	public void addGraphHeaders(Collection<GraphHeader> headers) {
		Validate.notNull(headers);

        try (Statement statement = connection.createStatement()){
            for (GraphHeader graphHeader : headers) {
                statement.execute(String.format(INSERT_GRAPH, graphHeader.getId(), graphHeader.getGraphModelId(), graphHeader.isDirected(), graphHeader.isRoot()));
            }
            connection.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
	}
	
	public void addVertexHeaders(Collection<VertexHeader> headers) {
        Validate.notNull(headers);

        try (Statement statement = connection.createStatement()){
            for (VertexHeader vertexHeader : headers) {
                statement.execute(String.format(INSERT_VERTEX, vertexHeader.getId(), vertexHeader.getGraphId(), vertexHeader.getLink2InnerGraph()));
            }
            connection.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
	}
	
	public void addEdgeHeaders(Collection<EdgeHeader> headers) {
        Validate.notNull(headers);

        try (Statement statement = connection.createStatement()){
            for (EdgeHeader edgeHeader : headers) {
                statement.execute(String.format(INSERT_EDGE, edgeHeader.getId(), edgeHeader.getGraphId(), edgeHeader.getSourceId(), edgeHeader.getTargetId()));
            }
            connection.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
	}
	
	public void addAttributeHeaders(Collection<AttributeHeader> headers) {
        Validate.notNull(headers);

        try (Statement statement = connection.createStatement()){
            for (AttributeHeader attributeHeader : headers) {
                statement.execute(String.format(INSERT_ATTRIBUTE,
                        attributeHeader.getId(),
                        attributeHeader.getOwnerId(),
                        attributeHeader.getOwnerType(),
                        attributeHeader.getName(),
                        attributeHeader.getStringValue(),
                        attributeHeader.getType()));
            }
            connection.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
	}

//=============================================================================
//--------------MODIFYING METHODS----------------------------------------------
	public boolean modifyGraphModelHeader(GraphModelHeader newGraphModelHeader) {
        throw new NotImplementedException("Method is not implemented");
//		if (newGraphModelHeader == null) return false;
//
//		String request = "UPDATE " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE +
//						 " SET " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_NAME + "='" + newGraphModelHeader.getName() + "' " +
//						 " WHERE " + GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_ID + "=" + newGraphModelHeader.getId() + ";";
//
//		return commit_one_request(request);
	}
	
	public boolean modifyVertexHeader(VertexHeader newVertexHeader) {
        throw new NotImplementedException("Method is not implemented");
//		if (newVertexHeader == null) return false;
//
//		String request = "UPDATE " + GraphDataBaseFormat_1_0.VERTEX_TABLE +
//						 " SET " + GraphDataBaseFormat_1_0.VERTEX_TABLE_GRAPH_ID + "='" + newVertexHeader.getGraphModelId() + "', " +
//								   GraphDataBaseFormat_1_0.VERTEX_TABLE_LINK2INNER_GRAPH + "=" + newVertexHeader.getLink2InnerGraph() +
//						 " WHERE " + GraphDataBaseFormat_1_0.VERTEX_TABLE_ID + "=" + newVertexHeader.getId() + ";";
//
//		return commit_one_request(request);
	}
	
	public boolean modifyAttributeHeader(AttributeHeader newAttributeHeader) {
        throw new NotImplementedException("Method is not implemented");
//		if (newAttributeHeader == null) return false;
//
//		String request = "UPDATE " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE +
//						 " SET " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_STRING_VALUE + "='" + newAttributeHeader.toNotNullStringValue() + "', " +
//								   GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE + "='" + newAttributeHeader.getType() + "'" +
//						 " WHERE " + GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_ID + "=" + newAttributeHeader.getId() + ";";
//
//		return commit_one_request(request);
	}
	
//==============================================================================
//------------------GET DATA----------------------------------------------------
    public List<GraphModelHeader> getGraphModelHeaders(int count) {
        List<GraphModelHeader> result = Lists.newArrayList();

        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_GRAPH_MODELS));
            while(resultSet.next()) {
                result.add(new GraphModelHeader(resultSet.getInt(GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_ID), resultSet.getString(GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_NAME)));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return result;
    }


    public GraphModelHeader getGraphModelHeader(int graphModelId) {
        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_GRAPH_MODEL_BY_ID, graphModelId));
            if (resultSet.next()) {
                return new GraphModelHeader(resultSet.getInt(GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_ID), resultSet.getString(GraphDataBaseFormat_1_0.GRAPH_MODEL_TABLE_NAME));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return null;
	}
	
	public GraphHeader getGraphHeader(int graphId) {
        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_GRAPH_BY_ID, graphId));
            if (resultSet.next()) {
                return new GraphHeader(resultSet.getInt(GraphDataBaseFormat_1_0.GRAPH_TABLE_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.GRAPH_TABLE_MODEL_ID),
                        resultSet.getBoolean(GraphDataBaseFormat_1_0.GRAPH_TABLE_DIRECTED),
                        resultSet.getBoolean(GraphDataBaseFormat_1_0.GRAPH_TABLE_IS_ROOT));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return null;
	}
	
	public VertexHeader getVertexHeader(int vertexId) {
        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_VERTEX_BY_ID, vertexId));
            if (resultSet.next()) {
                return new VertexHeader(resultSet.getInt(GraphDataBaseFormat_1_0.VERTEX_TABLE_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.VERTEX_TABLE_GRAPH_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.VERTEX_TABLE_LINK2INNER_GRAPH));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return null;
	}

    public VertexHeader getVertexHeaderByGraphId(int vertexId) {
        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_VERTEX_BY_GRAPH_ID, vertexId));
            if (resultSet.next()) {
                return new VertexHeader(resultSet.getInt(GraphDataBaseFormat_1_0.VERTEX_TABLE_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.VERTEX_TABLE_GRAPH_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.VERTEX_TABLE_LINK2INNER_GRAPH));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return null;
    }
	
	public EdgeHeader getEdgeHeader(int edgeId) {
        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_EDGE_BY_ID, edgeId));
            if (resultSet.next()) {
                return new EdgeHeader(resultSet.getInt(GraphDataBaseFormat_1_0.EDGE_TABLE_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.EDGE_TABLE_GRAPH_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.EDGE_TABLE_SRC_VERTEX),
                        resultSet.getInt(GraphDataBaseFormat_1_0.EDGE_TABLE_TRG_VERTEX));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return null;
	}
	
	public AttributeHeader getAttributeHeader(int attributeId) {
        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_ATTRIBUTE_BY_ID, attributeId));
            if (resultSet.next()) {
                return new AttributeHeader(resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE),
                        resultSet.getString(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME),
                        resultSet.getString(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_STRING_VALUE),
                        resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return null;
	}

    public List<AttributeHeader> getAttributeHeadersByOwner(int ownerId, int ownerType) {
        List<AttributeHeader> result = Lists.newArrayList();

        try (Statement statement = connection.createStatement()){
            ResultSet resultSet = statement.executeQuery(String.format(SELECT_ATTRIBUTES_BY_OWNER, ownerId, ownerType));
            while (resultSet.next()) {
                result.add(new AttributeHeader(resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_ID),
                        resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_OWNER_TYPE),
                        resultSet.getString(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_NAME),
                        resultSet.getString(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_STRING_VALUE),
                        resultSet.getInt(GraphDataBaseFormat_1_0.ATTRIBUTE_TABLE_VALUE_TYPE)));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return result;
    }
}
